package com.xjrccb.meeting.dao;

import java.io.UnsupportedEncodingException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.UUID;

import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.xjrccb.meeting.bean.Department;
import com.xjrccb.meeting.bean.Meeting;
import com.xjrccb.meeting.bean.User;

public class AddMeetingDao  extends JdbcDaoSupport{
	
	public boolean insertMeeting(final Meeting meeting){
		int i = this.getJdbcTemplate().update("insert into meeting(id,place,meetingname,duration,startTime,endTime,persons) values(?,?,?,?,?,?,?)",   
                new PreparedStatementSetter(){  
              
                    @Override  
                    public void setValues(PreparedStatement ps) throws SQLException {  
                    	String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    	ps.setString(1, uuid); 
                        ps.setString(2, meeting.getPlace());  
                        ps.setString(3, meeting.getMeetingname());  
                        ps.setString(4, meeting.getDuration());
                        ps.setString(5, meeting.getStartTime());
                        ps.setString(6, meeting.getEndTime());
                        ps.setString(7, meeting.getPersons());
                    }  
        });  
		return i>0;
		
	}
	
	/**
	 * 查询时间区间会议室预订记录
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Meeting> queryMeeting(String startTime ,String endTime){
		return (List<Meeting>) this.getJdbcTemplate().query("select * from meeting where unix_timestamp(startTime) between unix_timestamp(?) and unix_timestamp(?) ",  
                new Object[]{startTime,endTime},  
                new int[]{java.sql.Types.VARCHAR,java.sql.Types.VARCHAR},  
                new RowMapper(){  
				            @Override  
				            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
				            	Meeting meeting  = new Meeting();  
				            	meeting.setId(rs.getString("id"));  
				            	meeting.setPlace(rs.getString("place"));  
//				            	try {
//									meeting.setMeetingname(new String(rs.getString("meetingname").getBytes(),"UTF-8"));
//								} catch (UnsupportedEncodingException e) {
//									// TODO Auto-generated catch block
//									e.printStackTrace();
//								}  
				            	meeting.setMeetingname(rs.getString("meetingname"));
				            	meeting.setStartTime(rs.getString("startTime"));  
				            	meeting.setEndTime(rs.getString("endTime"));  
				            	meeting.setRemark(rs.getString("remark"));  
				            	meeting.setDuration(rs.getString("duration"));  
				                return meeting;  
				            }  
				});
		
	}
	
	@SuppressWarnings("unchecked")
	public Meeting queryDetail(String id){
		RowMapper<Meeting> rm = ParameterizedBeanPropertyRowMapper.newInstance(Meeting.class);
		return (Meeting) this.getJdbcTemplate().queryForObject(
		                "SELECT * FROM Meeting WHERE ID = ?",rm,id);

		
	}
	
	public boolean deleteMeeting(String id) {  
		int i = this.getJdbcTemplate().update(  
                "delete from meeting where id = ?",   
                new Object[]{id},   
                new int[]{java.sql.Types.VARCHAR});  
		return i>0;
    } 

	@SuppressWarnings("unchecked")
	public List<User> queryUser(){
		return (List<User>) this.getJdbcTemplate().query("select a.*,b.name deptName from User a,department b where a.deptid=b.id order by initial",  
                new Object[]{ },  
                new int[]{ },  
                new RowMapper(){  
				            @Override  
				            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
				            	User user  = new User();  
				            	user.setId(rs.getString("id"));  
				            	user.setRealName(rs.getString("realName"));
				            	user.setUserName(rs.getString("userName"));  
				            	user.setUuid(rs.getString("uuid"));  
				            	user.setEmail(rs.getString("email")); 
				            	user.setInitial(rs.getString("initial"));
				            	user.setDeptName(rs.getString("deptName"));
				                return user;  
				            }  
				});
		
	}
	
	@SuppressWarnings("unchecked")
	public List<User> queryUserByDept(String id){
		return (List<User>) this.getJdbcTemplate().query("select * from User where deptid = ?",  
                new Object[]{id },  
                new int[]{ java.sql.Types.VARCHAR},  
                new RowMapper(){  
				            @Override  
				            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
				            	User user  = new User();  
				            	user.setId(rs.getString("id"));  
				            	user.setRealName(rs.getString("realName"));
				            	user.setUserName(rs.getString("userName"));  
				            	user.setUuid(rs.getString("uuid"));  
				            	user.setEmail(rs.getString("email")); 
				            	user.setInitial(rs.getString("initial"));
				                return user;  
				            }  
				});
	}
	
	@SuppressWarnings("unchecked")
	public List<Department> queryDept(){
		return (List<Department>) this.getJdbcTemplate().query("select * from Department ",  
                new Object[]{ },  
                new int[]{ },  
                new RowMapper(){  
				            @Override  
				            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
				            	Department dept  = new Department();  
				            	dept.setId(rs.getString("id"));  
				            	dept.setName(rs.getString("name"));
				            	dept.setParentId(rs.getString("parentId"));
				            	dept.setParentIds(rs.getString("parentIds"));
				                return dept;  
				            }  
				});

		
	}
}
